
using System;
using System.Linq;
using Dapper;
using MySqlConnector;
using Microsoft.Extensions.Logging;

namespace Infrastructure.Data
{
    public interface IDatabaseInitializer
    {
        void Initialize();
        MySqlConnection CreateConnection();
    }

    public class DapperInitializer : IDatabaseInitializer
    {
        private readonly string _connectionString;
        private readonly ILogger<DapperInitializer> _logger;

        public DapperInitializer(string connectionString, ILogger<DapperInitializer> logger = null)
        {
            _connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
            _logger = logger;
        }

        public void Initialize()
        {
            using (var connection = CreateConnection())
            {
                connection.Open();
                
                try
                {
                    // 1. 创建核心表结构
                    CreateCoreTables(connection);
                    
                    // 2. 建立表关系
                    EstablishTableRelationships(connection);
                    
                    // 3. 初始化基础数据
                    SeedInitialData(connection);
                    
                    _logger?.LogInformation("数据库初始化完成");
                }
                catch (Exception ex)
                {
                    _logger?.LogError(ex, "数据库初始化失败");
                    throw;
                }
            }
        }

        private void CreateCoreTables(MySqlConnection connection)
        {
            var tables = new[]
            {
                @"CREATE TABLE IF NOT EXISTS Workflow (
                    Id INT AUTO_INCREMENT PRIMARY KEY,
                    Title VARCHAR(100) NOT NULL,
                    Description TEXT,
                    Status INT NOT NULL,
                    CreatorId INT NOT NULL,
                    CreatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    UpdatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB",

                @"CREATE TABLE IF NOT EXISTS ApprovalHistory (
                    Id INT AUTO_INCREMENT PRIMARY KEY,
                    WorkflowId INT NOT NULL,
                    ApproverId INT NOT NULL,
                    Status INT NOT NULL,
                    Comments VARCHAR(500),
                    CreatedAt DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_workflow_id (WorkflowId)
                ) ENGINE=InnoDB"
            };

            foreach (var sql in tables)
            {
                connection.Execute(sql);
            }
        }

        private void EstablishTableRelationships(MySqlConnection connection)
        {
            try
            {
                connection.Execute(@"
                    ALTER TABLE ApprovalHistory 
                    ADD CONSTRAINT fk_approval_workflow
                    FOREIGN KEY (WorkflowId) REFERENCES Work flow(Id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE");
            }
            catch (MySqlException ex) when (ex.Number == 1215) // 外键约束错误
            {
                _logger?.LogWarning("外键约束创建失败，改用触发器校验: {Message}", ex.Message);
                
                connection.Execute(@"
                    CREATE TRIGGER IF NOT EXISTS tr_validate_workflow_id
                    BEFORE INSERT ON ApprovalHistory
                    FOR EACH ROW
                    BEGIN
                        IF NOT EXISTS (SELECT 1 FROM Workflow WHERE Id = NEW.WorkflowId) THEN
                            SIGNAL SQLSTATE '45000'
                            SET MESSAGE_TEXT = 'Invalid WorkflowId';
                        END IF;
                    END");
            }
        }

        private void SeedInitialData(MySqlConnection connection)
        {
            // 可添加基础数据初始化逻辑
        }

        public MySqlConnection CreateConnection() => new MySqlConnection(_connectionString);
    }
}
